import pymysql
import datetime
from faker import Faker
import random
faker1 = Faker(locale = 'zh_cn')


# 1、通过python连接pymysql数据库，封装类方法
# 1.1、随机插入100条数据到数据库表，包括姓名、年龄、家庭住址、身份证号码
# 1.2、查询全部的数据
# 1.3、删除指定一条数据
# 1.4、封装批量删除方法，可以删除多条
# 1.5、可以指定增加单独一条数据到表里
# 以上每个操作步骤要写日志信息到log.txt里
# db =pymysql.connect(host = "121.41.51.49",user = "root",password="Aa010903!",database="day8_1",cursorclass=pymysql.cursors.DictCursor)
# cursor =db.cursor()

"""# 1、通过python连接pymysql数据库，封装类方法
class DB:
    def __init__(self,host,user,passward,databases,cursorclass):
        self.host = host
        self.user = user
        self.passward = passward
        self.databases = databases
        self.cursorclass = cursorclass
    def connect_(self):
        self.db =pymysql.connect(host=self.host,user=self.user,password=self.passward,database=self.databases,cursorclass=self.cursorclass)
        self.cursor =self.db.cursor()
        with open("day8_log.txt","a",encoding="utf-8") as f:
            f.write(f"创建数据库对象：{self.db},时间为：{datetime.datetime.now()}\n")
    def close_(self):
        self.cursor.close()
        self.db.close()
        with open("day8_log.txt","a",encoding="utf-8") as f:
            f.write(f"关闭数据库对象{self.db},关闭时间为：{datetime.datetime.now()}\n")
    def create_(self,sql):
        self.connect_()
        try:
            self.cursor.execute(sql)
            with open("day8_log.txt","a",encoding="utf-8") as f:
                f.write(f"创建表成功，时间为：{datetime.datetime.now()}\n")
            print("创建表成功")
            self.close_()
        except Exception as e:
            self.db.rollback()
            with open("day8_log.txt","a",encoding="utf-8") as f:
                f.write(f"创建表失败，时间为：{datetime.datetime.now()}\n")
            self.close_()
            print(f"sql语句执行失败，错误类型为{e}")
            return 0

    # 1.1、随机插入100条数据到数据库表，包括姓名、年龄、家庭住址、身份证号码
    def insert_random(self,table):
        self.connect_()
        try:
            for i in range(100):
                name = faker1.name()
                id_msg = str(faker1.ssn(min_age=18, max_age=90))   # 6-15
                # print(id_msg)
                id1 = str(id_msg)[6:10]
                print(id1)
                time_now = str(datetime.datetime.now())
                time1 = time_now.replace("-","")[0:4]
                print(time1)
                age = int(time1)- int(id1)
                print(age)
                address = faker1.address()
                print(address)
                sql = f"insert into {table} values('{name}',{age},'{address}','{id_msg}');"
                self.cursor.execute(sql)
            self.db.commit()
            with open("day8_log.txt","a",encoding="utf-8") as f:
                f.write(f"插入了一百条数据，时间为：{datetime.datetime.now()}\n")
            self.close_()
        except Exception as e:
            self.db.rollback()
            with open("day8_log.txt","a",encoding="utf-8") as f:
                f.write(f"插入数据失败，时间为：{datetime.datetime.now()}\n")
            print(f"插入随机100条数据失败，错误类型为:{e}")
            self.close_()
    # 1.2、查询全部的数据
    def get_all(self,sql):
        self.connect_()
        try:
            self.cursor.execute(sql)
            result = self.cursor.fetchall()
            self.close_()
            print("查询成功！")
            with open("day8_log.txt","a",encoding="utf-8") as f:
                f.write(f"查询全部数据成功，时间为：{datetime.datetime.now()}\n")
            return result
        except Exception as e:
            self.db.rollback()
            self.close_()
            print(f"查询全部数据失败！错误类型为：{e}")
            with open("day8_log.txt","a",encoding="utf-8") as f:
                f.write(f"查询全部数据失败，时间wei:{datetime.datetime.now()}\n")
    # 1.3、删除指定一条数据
    def main_(self,sql):
        self.connect_()
        try:
            self.cursor.execute(sql)
            self.db.commit()
            self.close_()
            return "成功"
        except Exception as e:
            self.db.rollback()
            self.close_()
            print(f"sql执行错误，错误类型为:{e}")
            return "发生错误"
    def delete_(self,sql):
        result = self.main_(sql)
        with open("day8_log.txt","a",encoding="utf-8") as f:
            f.write(f"指定删除一条{result},时间为：{datetime.datetime.now()}\n")
        return result

    # 1.4、封装批量删除方法，可以删除多条
    def delete_more(self,table,num):
        self.connect_()
        sql = f"select * from {table}"
        self.cursor.execute(sql)
        result = self.cursor.fetchall()
        try:
            count = 0
            for i in result:
                if count < num:
                    count += 1
                    name = i["name"]
                    sql = f"delete from {table} where name ='{name}'"
                    self.cursor.execute(sql)
            self.db.commit()
            with open("day8_log.txt","a",encoding="utf-8") as f:
                f.write(f"删除{table}表中{num}条数据成功，时间是{datetime.datetime.now()}")
            print(f"删除{num}条数据成功！")
            self.close_()
        except Exception as e:
            self.db.rollback()
            self.close_()
            with open("day8_log.txt","a",encoding="utf-8") as f:
                f.write(f"删除{table}表中{num}条数据失败，时间是{datetime.datetime.now()}\n")
            print(f"删除{num}条数据失败,{e}")
    # 1.5、可以指定增加单独一条数据到表里
    def insert_one(self,sql):
        result = self.main_(sql)
        print(f"指定增加一条数据{result}，时间为：{datetime.datetime.now()}\n")
        with open("day8_log.txt","a",encoding="utf-8") as f:
            f.write(f"指定增加一条{result},时间为：{datetime.datetime.now()}\n")
        return result

if __name__ == '__main__':
    # def __init__(self, host, user, passward, databases, cursorclass):
    d1 = DB(host = "121.41.51.49",user = "root",passward="Aa010903!",databases="day8_1",cursorclass=pymysql.cursors.DictCursor)
    # d1.insert_random("message2")
    # d1.create_("create table message2(name char(50) primary key,age int(3) not null,address char(50) not null,id_msg char(50) not null)")
    # print(d1.get_all("select * from message1"))
    # print(d1.delete_("delete from message2 where age = 50"))
    # d1.delete_more("message1",5)
    d1.insert_one("insert into message1 values('丁',19,'沈阳','123213234234')")"""

#
# 2、做一个学生点名系统，系统里支持：存入学生、更改学生、删除学生、点到学生姓名方法
#
# 存入学生（姓名、年龄、家庭地址、手机号）： 存入成功后，提示xxx存入成功
# 更改学生（姓名、年龄、家庭地址、手机号）：可以修改学生内容的任意一个字段
# 删除学生：删除的学生将不在展示
# 展示全部学生：可以看到全部的学生姓名
# 学生姓名：随机抽取任意一个存在的学生信息，然后打印：到！。举例： 刘闯到！
#
# 以上内容，需要连接数据库，每次的数据都需要增、删、改、查去做

# db =pymysql.connect(host = "121.41.51.49",user = "root",password="Aa010903!",database="day8_1",cursorclass=pymysql.cursors.DictCursor)
"""class DbStu:
    def __init__(self,host,user,password,databases,cursorclass):
        self.host = host
        self.user = user
        self.password = password
        self.databases = databases
        self.cursorclass = cursorclass
    def connect_(self):
        self.db = pymysql.connect(host = "121.41.51.49",user = "root",password="Aa010903!",database="day8_2",cursorclass=pymysql.cursors.DictCursor)
        self.cursor = self.db.cursor()
    def close_(self):
        self.cursor.close()
        self.db.close()
    def create_(self,sql):
        self.connect_()
        try:
            self.cursor.execute(sql)
            self.close_()
            print(f"创建表成功！")
        except Exception as e:
            self.db.rollback()
            self.close_()
            print(f"创建表失败，错误类型是:{e}")

    # 主函数
    def main_(self,sql):
        self.connect_()
        try:
            self.cursor.execute(sql)
            self.db.commit()
            self.close_()
            return f"成功！"
        except Exception as e:
            self.db.rollback()
            self.close_()
            return f"失败，错误类型是：{e}"

    # 存入学生（姓名、年龄、家庭地址、手机号）： 存入成功后，提示xxx存入成功
    def insert_(self,sql):
        result = self.main_(sql)
        print(f"存入{result}")

    # 更改学生（姓名、年龄、家庭地址、手机号）：可以修改学生内容的任意一个字段
    def modify_(self,sql):
        result = self.main_(sql)
        print(f"更改{result}")

    # 删除学生：删除的学生将不在展示
    def delete_(self,sql):
        result = self.main_(sql)
        print(f"删除{result}")

    # 展示全部学生：可以看到全部的学生姓名
    def show_(self,table):
        self.connect_()
        try:
            sql = f"select * from {table}"
            self.cursor.execute(sql)
            result = self.cursor.fetchall()
            self.close_()
            return result
        except Exception as e:
            self.db.rollback()
            self.close_()
            return f"展示失败，错误类型是{e}"

    # 学生姓名：随机抽取任意一个存在的学生信息，然后打印：到！。举例： 刘闯到！
    def extract_(self,table):
        self.connect_()
        try:
            list_ = []
            sql = f"select * from {table}"
            self.cursor.execute(sql)
            result = self.cursor.fetchall()
            self.close_()
            for i in result:
                list_.append(i["name"])
            name = random.choice(list_)
            print(f"{name}到！")
        except Exception as e:
            self.db.rollback()
            self.close_()
            print(f"sql执行错误，错误类型为：{e}")
if __name__ == '__main__':
    d1 = DbStu(host = "121.41.51.49",user = "root",password="Aa010903!",databases="day8_2",cursorclass=pymysql.cursors.DictCursor)
    # d1.create_("create table stu_msg(name char(30) primary key,age int(2) not null,address char(50) not null,phone char(20) not null)")
    # d1.insert_("insert into stu_msg values('丁莹',18,'沈阳','1231234234')")
    # d1.insert_("insert into stu_msg values('沈腾',40,'齐齐哈尔','12324234')")
    # d1.modify_("update stu_msg set age = 38 where name = '沈腾'")
    # d1.delete_("delete from stu_msg where name = '艾伦'")
    # d1.extract_("stu_msg")"""

#
# 3、类作业，统计镰刀和斧头的数量，要使用类方法
"""class Gun:
    L_count = 0
    F_count = 0
    @classmethod
    def count(cls):
        print(f"斧头的数量是：{cls.F_count},镰刀的数量是：{cls.L_count}")
class L(Gun):
    def __init__(self):
        Gun.L_count += 1
class F(Gun):
    def __init__(self):
        Gun.F_count += 1
if __name__ == '__main__':
    a1 = F()
    a2 = L()
    a3 = L()
    print(Gun.count())"""


# 4、新建学生成绩管理系统，随机生成100个 学生信息插入到数据库：
# 学生信息组成：学号、学生姓名、英语成绩、数学成绩、语文成绩、班级号（班级号有：1809A，1809B，1810A，1810B）
#
# 成绩划分为：优秀，良好，中等，及格，不及格。良好比中等好。
#
# 优秀：90—100之间（包括90）
# 良好：80-90
# 中等：70-80
# 及格：60-70
# 不合格：分数低于60分。
#
# 成绩管理系统提供以下方法：
# 《1》 查询所有英语成绩为优秀的、良好的、中等、及格和不合格的学生姓名和班级号

# 《2》 查询1809A，1809B，1810A，1810B每个班级的数学成绩，并且是降序返回
# 《3》 查询1809A，1809B，1810A，1810B每个班级的总人数，并且由大到小排序返回
# 《4》 查询英语、数学、语文成绩不合格的学生姓名和对应的班级号，按照不合格的人数，由大到小排序
# db =pymysql.connect(host = "121.41.51.49",user = "root",password="Aa010903!",database="day8_1",cursorclass=pymysql.cursors.DictCursor)

"""class Stu:
    def __init__(self,host,user,password,databases,cursorclass):
        self.host = host
        self.user = user
        self.password = password
        self.databases = databases
        self.cursorclass = cursorclass
    def connect_(self):
        self.db = pymysql.connect(host = "121.41.51.49",user = "root",password="Aa010903!",database="day8_4",cursorclass=pymysql.cursors.DictCursor)
        self.cursor = self.db.cursor()
    def close_(self):
        self.cursor.close()
        self.db.close()
    def insert_100(self):
        self.connect_()
        try:
            for i in range(100):
                id = i + 1
                name = faker1.name()
                english_ = random.randint(0,100)
                math_ = random.randint(0,100)
                chinese_ = random.randint(0,100)
                list_ = ["1809A","1809B","1810A","1810B"]
                class_id = random.choice(list_)
                sql = f"insert into stu_msg values({id},'{name}',{english_},{math_},{chinese_},'{class_id}')"
                self.cursor.execute(sql)
            self.db.commit()
            self.close_()
            print(f"一百条数据插入成功！")
        except Exception as e:
            self.db.rollback()
            self.close_()
            print(f"一百条数据插入失败，错误类型是：{e}")

    # 《1》 查询所有英语成绩为优秀的、良好的、中等、及格和不合格的学生姓名和班级号
    def english_(self):
        self.connect_()
        try:
            sql1 = "select name,class_id from stu_msg where english >= 90"
            self.cursor.execute(sql1)
            result1 = self.cursor.fetchall()
            self.close_()
            print(f"英语成绩优秀的为：{result1}\n")
            self.connect_()
            sql2 = "select name,class_id from stu_msg where english >= 80 and english < 90"
            self.cursor.execute(sql2)
            result2 = self.cursor.fetchall()
            self.close_()
            print(f"英语成绩为良好为：{result2}\n")
        except Exception as e:
            self.db.rollback()
            self.close_()
            print(f"查询英语成绩等级错误，错误类型为：{e}")
    # 《2》 查询1809A，1809B，1810A，1810B每个班级的数学成绩，并且是降序返回
    def math_(self,class_id):
        self.connect_()
        try:
            sql = f"select class_id,math from stu_msg where class_id = '{class_id}' order by math desc"
            self.cursor.execute(sql)
            result = self.cursor.fetchall()
            self.close_()
            print(f"班级{class_id}的数学成绩排名为：{result}")
        except Exception as e:
            self.db.rollback()
            self.close_()
            print(f"查询数学成绩错误，错误类型为：{e}")
    # 《3》 查询1809A，1809B，1810A，1810B每个班级的总人数，并且由大到小排序返回
    def people_num(self):
        self.connect_()
        try:
            # sql = "select count(s_id) as '人数' from stu_msg group by class_id order by count(s_id) desc"
            sql = "select c.class_id as '班级',count(s_id) as '人数' from (select '1809A' as class)"
            self.cursor.execute(sql)
            result = self.cursor.fetchall()
            self.close_()
            print(f"各班级人数排名：{result}")
        except Exception as e:
            self.db.rollback()
            self.close_()
            print(f"查询各班级人数错误，错误类型为：{e}")
    # 《4》 查询英语、数学、语文成绩不合格的学生姓名和对应的班级号，按照不合格的人数，由大到小排序
    def fail_(self,course):
        self.connect_()
        try:
            sql = f"select class_id,{course},name from stu_msg where {course} < 60"
            self.cursor.execute(sql)
            result = self.cursor.fetchall()
            self.close_()
            print(f"每个班{course}成绩不合格学生:{result}")
        except Exception as e:
            self.db.rollback()
            self.close_()
            print(f"执行错误，错误类型是:{e}")

if __name__ == '__main__':
    s1 = Stu(host = "121.41.51.49",user = "root",password="Aa010903!",databases="day8_1",cursorclass=pymysql.cursors.DictCursor)
    # s1.insert_100()
    # s1.english_()
    # s1.math_("1809A")
    # s1.people_num()
    s1.fail_("math")"""


#
# 5、定义一个图书管理系统类，图书可以新增、修改、删除，要求新增、修改、删除的图书，都要出现在数据库中
# 新增：图书名、作者名称、新建年月日
# 修改：图书名、作者名称、新建年月日
# 删除：图书删掉，且不在展示
# 查看：全量查询、精确查询、模糊查询需要用户传入形参来控制返回结果

class Book:# host = "121.41.51.49",user = "root",password="Aa010903!",database="day8_1",cursorclass=pymysql.cursors.DictCursor
    def __init__(self,host,user,password,databases,cursorclass):
        self.host = host
        self.user = user
        self.password = password
        self.databases = databases
        self.cursorclass = cursorclass
    def connect_(self):
        self.db = pymysql.connect(host = "121.41.51.49",user = "root",password="Aa010903!",database="day8_5",cursorclass=pymysql.cursors.DictCursor)
        self.cursor = self.db.cursor()
    def close_(self):
        self.cursor.close()
        self.db.close()

    # 新增：图书名、作者名称、新建年月日
    def add_(self,book,name,time):
        self.connect_()
        sql = "select book from book"
        self.cursor.execute(sql)
        result = self.cursor.fetchall()
        list_ = []
        for i in result:
            list_.append(i["book"])
        if book in list_:
            print("该图书已经存在！")
        else:
            sql1 = f"insert into book values('{book}','{name}','{time}')"
            self.cursor.execute(sql1)
            self.db.commit()
            print("新增成功！")
        self.close_()
    # 修改：图书名、作者名称、新建年月日
    def modify_(self,book,name,time):
        self.connect_()
        sql = "select book from book"
        self.cursor.execute(sql)
        result = self.cursor.fetchall()
        list_1 = []
        for i in result:
            list_1.append(i["book"])
        if book not in list_1:
            print("您要修改的图书不存在！")
        else:
            sql1 = f"update book set name = '{name}',time_ = {time} where book = '{book}'"
            self.cursor.execute(sql1)
            self.db.commit()
            print("修改成功！")
        self.close_()

    # 删除：图书删掉，且不在展示
    def delete_(self,book):
        self.connect_()
        sql = f'delete from book where book ="{book}"'
        self.cursor.execute(sql)
        self.db.commit()
        self.close_()
        print(f"删除{book}成功")
    # 查看：全量查询、精确查询、模糊查询需要用户传入形参来控制返回结果
    # 全量查看
    # def get_(self,*args):
        # if args:
        # self.cursor.execute('select * from book')
    #
    # name_in = input("请输入要查找的图书名：")
    # # 精确查询
    # cursor.execute(f'select *from book where book ="{name_in}"')
    # # 模糊查询
    # cursor.execute(f'select *from book where book like "%{name_in}%"')
    # cursor.close()
    # db.close()


if __name__ == '__main__':
    b1 = Book(host = "121.41.51.49",user = "root",password="Aa010903!",databases="day8_5",cursorclass=pymysql.cursors.DictCursor)
    # b1.add_("喜羊羊","灰太狼","2025/4/7")
    # b1.add_("喜羊羊","灰太狼","2025/4/7")
    # b1.modify_("喜羊羊","红太狼","2020/2/9")
    # b1.modify_("buzhidao","红太狼","2020/2/9")
    # b1.delete_("喜羊羊")
